if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_leaveDeductJob')
          and type in ('P','PC'))
   drop procedure bd_proc_leaveDeductJob
go


create procedure bd_proc_leaveDeductJob as
BEGIN
	
	DECLARE @currDate varchar(10) = convert(varchar(10),GETDATE(),120)
	DECLARE  @user_serial bigint
		--  异常扑捉机制
		BEGIN TRY
			BEGIN TRAN
				--待扣除的人员名单和对应的值
				create table #leave_deduct(
					user_serial bigint not null,
					ho_type int not null
				)
				--将历史表中的currDate日期存在记录的人，针对这些人重新计算。
				insert into #leave_deduct
				select  user_serial,ho_type from bd_kq_holidays
					where data_status !=1 and convert(varchar(10),ho_date,120) = @currDate
				--重新计算调休
				DECLARE userOfReset1 CURSOR LOCAL FOR 
				select DISTINCT user_serial from #leave_deduct where ho_type in (21,22,23,24);

				OPEN userOfReset1 FETCH NEXT FROM #userOfReset1 INTO @user_serial  
				WHILE @@fetch_status = 0 
					BEGIN
							--重新计算
							exec bd_proc_reset_data @currDate,1,@user_serial,''
							
						FETCH NEXT FROM userOfReset1 INTO @user_serial
					END
				--从新计算年假	
				DECLARE userOfReset2 CURSOR LOCAL FOR 
				select DISTINCT user_serial from #leave_deduct where ho_type in (1,10,11,31,32,33,41,42,43,44);

				OPEN userOfReset2 FETCH NEXT FROM #userOfReset2 INTO @user_serial  
				WHILE @@fetch_status = 0 
					BEGIN
							--重新计算
							exec bd_proc_reset_data @currDate,2,@user_serial,''
							
						FETCH NEXT FROM userOfReset2 INTO @user_serial
					END
			
			--  提交事务
			COMMIT TRAN

		--  结束异常捕捉
		END TRY
		
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
		END CATCH
END
go
